#!/bin/bash
export HIVE_HOME=/usr/bin/hive

if [ $# == 1 ]
then
   dateStr=$1
else
   dateStr=`date -d'-1 day' +'%Y-%m-%d'`
fi


echo ${dateStr}

hiveSql="
-- web_chat_ems
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;

set hive.exec.orc.compression.strategy=COMPRESSION;

set hive.enforce.bucketing=true;

set hive.enforce.sorting=true;


-- 第二次拉链表

--1,员工表,  是分区表,是拉链表,
Insert Overwrite Table edu_dwd.dim_employee Partition (start_date)
Select id,
       email,
       real_name,
       phone,
       department_id,
       department_name,
       remote_login,
       job_number,
       cross_school,
       last_login_date,
       creator,
       create_date_time,
       update_date_time,
       deleted,
       scrm_department_id,
       leave_office,
       leave_office_time,
       reinstated_time,
       superior_leaders_id,
       tdepart_id,
       tenant,
       ems_user_name,
       '9999-99-99' As end_date,
       dt as start_date
From edu_ods.employee
Where dt = '${dateStr}'
Union All
Select e1.id,
       email,
       real_name,
       phone,
       department_id,
       department_name,
       remote_login,
       job_number,
       cross_school,
       last_login_date,
       creator,
       create_date_time,
       update_date_time,
       deleted,
       scrm_department_id,
       leave_office,
       leave_office_time,
       reinstated_time,
       superior_leaders_id,
       tdepart_id,
       tenant,
       ems_user_name,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.dim_employee e1
         Left Join (Select id,
                           dt
                    From edu_ods.employee
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;


-- 2,,edu_dwd.itcast_school,,学校信息
Insert Overwrite Table edu_dwd.dim_itcast_school Partition (start_date)
Select id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       code,
       tenant,
       '9999-99-99' As end_date,
       dt as start_date
From edu_ods.itcast_school
Where dt = '${dateStr}'
Union All
Select e1.id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       code,
       tenant,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.dim_itcast_school e1
         Left Join (Select id,
                           dt
                    From edu_ods.itcast_school
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

-- 3,,,itcast_subject,学科表
Insert Overwrite Table edu_dwd.dim_itcast_subject Partition (start_date)
Select id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       code,
       tenant,
       '9999-99-99' As end_date,
       dt as start_date
From edu_ods.itcast_subject
Where dt = '${dateStr}'
Union All
Select e1.id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       code,
       tenant,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.dim_itcast_subject e1
         Left Join (Select id,
                           dt
                    From edu_ods.itcast_subject
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

-- 4,,scrm_department,部门表
Insert Overwrite Table edu_dwd.dim_scrm_department Partition (start_date)
Select id,
       name,
       parent_id,
       create_date_time,
       update_date_time,
       deleted,
       id_path,
       tdepart_code,
       creator,
       depart_level,
       depart_sign,
       depart_line,
       depart_sort,
       disable_flag,
       tenant,
       '9999-99-99' As end_date,
       dt as start_date
From edu_ods.scrm_department
Where dt = '${dateStr}'
Union All
Select e1.id,
       name,
       parent_id,
       create_date_time,
       update_date_time,
       deleted,
       id_path,
       tdepart_code,
       creator,
       depart_level,
       depart_sign,
       depart_line,
       depart_sort,
       disable_flag,
       tenant,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.dim_scrm_department e1
         Left Join (Select id,
                           dt
                    From edu_ods.scrm_department
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;


--5,, customer
Insert Overwrite Table edu_dwd.fact_customer partition (start_date)
Select id,
       customer_relationship_id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       idcard,
       birth_year,
       gender,
       phone,
       wechat,
       qq,
       email,
       area,
       leave_school_date,
       graduation_date,
       bxg_student_id,
       creator,
       origin_type,
       origin_channel,
       tenant,
       md_id,
       Substr(create_date_time, 1, 4)  As yearinfo,
       cast(Quarter(create_date_time) as string)       As quarterinfo,
       Substr(create_date_time, 6, 2)  As monthinfo,
       Substr(create_date_time, 9, 2)  As dayinfo,
       Substr(create_date_time, 12, 2) As hourinfo,
       '9999-99-99'                    As end_date,
       dt as start_date
From edu_ods.customer
Where dt = '${dateStr}'
Union All
Select e1.id,
       customer_relationship_id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       idcard,
       birth_year,
       gender,
       phone,
       wechat,
       qq,
       email,
       area,
       leave_school_date,
       graduation_date,
       bxg_student_id,
       creator,
       origin_type,
       origin_channel,
       tenant,
       md_id,
       yearinfo,
       quarterinfo,
       monthinfo,
       dayinfo,
       hourinfo,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.fact_customer e1
         Left Join (Select id,
                           dt
                    From edu_ods.customer
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

-- 6,,customer_appeal
Insert Overwrite Table edu_dwd.fact_customer_appeal partition (start_date)
Select id,
       customer_relationship_first_id,
       employee_id,
       employee_name,
       employee_department_id,
       employee_tdepart_id,
       appeal_status,
       audit_id,
       audit_name,
       audit_department_id,
       audit_department_name,
       audit_date_time,
       create_date_time,
       update_date_time,
       deleted,
       tenant,
       Substr(create_date_time, 1, 4)  As yearinfo,
        cast(Quarter(create_date_time) as string)       As quarterinfo,
       Substr(create_date_time, 6, 2)  As monthinfo,
       Substr(create_date_time, 9, 2)  As dayinfo,
       Substr(create_date_time, 12, 2) As hourinfo,
       '9999-99-99'                    As end_date,
       dt as start_date
From edu_ods.customer_appeal
Where dt = '${dateStr}'
Union All
Select e1.id,
       customer_relationship_first_id,
       employee_id,
       employee_name,
       employee_department_id,
       employee_tdepart_id,
       appeal_status,
       audit_id,
       audit_name,
       audit_department_id,
       audit_department_name,
       audit_date_time,
       create_date_time,
       update_date_time,
       deleted,
       tenant,
       yearinfo,
       quarterinfo,
       monthinfo,
       dayinfo,
       hourinfo,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.fact_customer_appeal e1
         Left Join (Select id,
                           dt
                    From edu_ods.customer_appeal
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

-- 7,,customer_clue
Insert Overwrite Table edu_dwd.fact_customer_clue partition (start_date)
Select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       customer_relationship_id,
       session_id,
       sid,
       status,
       `user`,
       create_time,
       platform,
       s_name,
       seo_source,
       seo_keywords,
       ip,
       referrer,
       from_url,
       landing_page_url,
       url_title,
       to_peer,
       manual_time,
       begin_time,
       reply_msg_count,
       total_msg_count,
       msg_count,
       comment,
       finish_reason,
       finish_user,
       end_time,
       platform_description,
       browser_name,
       os_info,
       area,
       country,
       province,
       city,
       creator,
       name,
       idcard,
       phone,
       itcast_school_id,
       itcast_school,
       itcast_subject_id,
       itcast_subject,
       wechat,
       qq,
       email,
       gender,
       level,
       origin_type,
       information_way,
       working_years,
       technical_directions,
       customer_state,
       valid,
       anticipat_signup_date,
       clue_state,
       scrm_department_id,
       superior_url,
       superior_source,
       landing_url,
       landing_source,
       info_url,
       info_source,
       origin_channel,
       course_id,
       course_name,
       zhuge_session_id,
       is_repeat,
       tenant,
       activity_id,
       activity_name,
       follow_type,
       shunt_mode_id,
       shunt_employee_group_id,
       Substr(create_date_time, 1, 4)  As yearinfo,
        cast(Quarter(create_date_time) as string)       As quarterinfo,
       Substr(create_date_time, 6, 2)  As monthinfo,
       Substr(create_date_time, 9, 2)  As dayinfo,
       Substr(create_date_time, 12, 2) As hourinfo,
       '9999-99-99'                    As end_date,
       dt as start_date
From edu_ods.customer_clue
Where dt = '${dateStr}'
Union All
Select e1.id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       customer_relationship_id,
       session_id,
       sid,
       status,
       `user`,
       create_time,
       platform,
       s_name,
       seo_source,
       seo_keywords,
       ip,
       referrer,
       from_url,
       landing_page_url,
       url_title,
       to_peer,
       manual_time,
       begin_time,
       reply_msg_count,
       total_msg_count,
       msg_count,
       comment,
       finish_reason,
       finish_user,
       end_time,
       platform_description,
       browser_name,
       os_info,
       area,
       country,
       province,
       city,
       creator,
       name,
       idcard,
       phone,
       itcast_school_id,
       itcast_school,
       itcast_subject_id,
       itcast_subject,
       wechat,
       qq,
       email,
       gender,
       level,
       origin_type,
       information_way,
       working_years,
       technical_directions,
       customer_state,
       valid,
       anticipat_signup_date,
       clue_state,
       scrm_department_id,
       superior_url,
       superior_source,
       landing_url,
       landing_source,
       info_url,
       info_source,
       origin_channel,
       course_id,
       course_name,
       zhuge_session_id,
       is_repeat,
       tenant,
       activity_id,
       activity_name,
       follow_type,
       shunt_mode_id,
       shunt_employee_group_id,
       yearinfo,
       quarterinfo,
       monthinfo,
       dayinfo,
       hourinfo,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.fact_customer_clue e1
         Left Join (Select id,
                           dt
                    From edu_ods.customer_clue
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

--8,, itcast_clazz
Insert Overwrite Table edu_dwd.fact_itcast_clazz partition (start_date)
Select
       id,
       create_date_time,
       update_date_time,
       deleted,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       itcast_brand,
       clazz_type_state,
       clazz_type_name,
       teaching_mode,
       start_time,
       end_time,
       comment,
       detail,
       uncertain,
       tenant,
       Substr(create_date_time, 1, 4)  As yearinfo,
        cast(Quarter(create_date_time) as string)       As quarterinfo,
       Substr(create_date_time, 6, 2)  As monthinfo,
       Substr(create_date_time, 9, 2)  As dayinfo,
       Substr(create_date_time, 12, 2) As hourinfo,
       '9999-99-99'                    As end_date,
       dt as start_date
From edu_ods.itcast_clazz
Where dt = '${dateStr}'
Union All
Select
       e1.id,
       create_date_time,
       update_date_time,
       deleted,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       itcast_brand,
       clazz_type_state,
       clazz_type_name,
       teaching_mode,
       start_time,
       end_time,
       comment,
       detail,
       uncertain,
       tenant,
        yearinfo,
       quarterinfo,
       monthinfo,
       dayinfo,
       hourinfo,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.fact_itcast_clazz e1
         Left Join (Select id,
                           dt
                    From edu_ods.itcast_clazz
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;

--9,, customer_relationship
Insert Overwrite Table edu_dwd.fact_customer_relationship partition (start_date)
Select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       Substr(create_date_time, 1, 4)  As yearinfo,
        cast(Quarter(create_date_time) as string)       As quarterinfo,
       Substr(create_date_time, 6, 2)  As monthinfo,
       Substr(create_date_time, 9, 2)  As dayinfo,
       Substr(create_date_time, 12, 2) As hourinfo,
       '9999-99-99'                    As end_date,
       dt as start_date
From edu_ods.customer_relationship
Where dt = '${dateStr}'
Union All
Select e1.id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
        yearinfo,
       quarterinfo,
       monthinfo,
       dayinfo,
       hourinfo,
       If(e2.id Is Not Null And e1.end_date = '9999-99-99', Date_Add(e2.dt, -1), e1.end_date) as end_date,
       start_date
From edu_dwd.fact_customer_relationship e1
         Left Join (Select id,
                           dt
                    From edu_ods.customer_relationship
                    Where dt = '${dateStr}') e2 On e1.id = e2.id;
"

${HIVE_HOME} -S -e "${hiveSql}"